import sqlite3
from tqdm import tqdm
import os 
import re

conn = sqlite3.connect('db.sqlite3')

c = conn.cursor()
c.execute("DELETE FROM web_book_data")

# Read Files
val_list = []
bookset = set()
fi = open('lib.txt', 'rt')

print('Reading from data...')
book_dict = {}

book_id = 0
for line in tqdm(fi.readlines()):
    try:
        lib_id, book_name, author, ISBN, publisher, year, user_name, user_id, _, _, _, _, _, _ = line.split('@')

        lib_id = re.match('[A-Z0-9./-]*', lib_id)[0]
        book_name = book_name.replace(';', '').replace("'","''")

        if not re.fullmatch('[0-9-]*', ISBN) or ISBN=='':
            continue
        publisher = publisher.replace(';', '').replace("'","''")
        author = author.replace(';', '').replace("'", "''")

        if not re.fullmatch('[0-9]*', year):
            year = '0000'

        book_str = f'{lib_id};{book_name};{ISBN}'
        if book_str in book_dict:
            book_dict[book_str][-3] += 1
        else:
            book_dict[book_str] = [book_id, lib_id, book_name, author, ISBN, publisher, year, 1, 0, "0.0"]
            book_id += 1
    
    except Exception as err:
        print(err)
        pass

print('Insert to DB...')

sql_id = 0
for (k, v) in tqdm(book_dict.items()):
    exe_str = f"""INSERT INTO web_book_data VALUES ({sql_id},{v[0]},'{v[1]}','{v[2]}','{v[3]}','{v[4]}','{v[5]}','{v[6]}',{v[7]},{v[8]},'{v[9]}')"""
    print(exe_str)
    c.execute(exe_str)
    sql_id += 1

conn.commit()
conn.close()
print("success")
